﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;

namespace BookStore.Controllers
{
    public class AuthorController : Controller
    {
        private static AuthorController _controller = null;
        public static AuthorController getController()
        {
            if (_controller == null)
                return new AuthorController();
            else
                return _controller;
        }
        public AuthorController()
        {
            _controller = this;
        }

        internal object getAllAuthor()
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Author", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        public String getAuthor(String author, int check)
        {
            DataTable dt = new DataTable("GetAuthor");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Get AuthorName from AuthorID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_GetAuthorName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = author;
                }
                // Get ManufacturerID from ManufacturerName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_GetAuthorID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = author;
                }
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if(dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return "";
        }

        public int getCountryCodeOfAuthor(ComboBox combobox, String countrycode)
        {
            for (int i = 0; i < combobox.Items.Count; i++)
            {
                String checkcode = combobox.Items[i].ToString().Substring(0, 2);
                if (countrycode.Length < 2)
                    return 0;
                else if (checkcode.Equals(countrycode.Substring(0,2)))
                    return i;
            }
            return 0;
        }

        public String getNumericCodeOfAuthor(String codeCountry)
        {
            String AuthorID;
            String convert;
            String code;
            Decimal numeric;
            DataTable dt = new DataTable("GetNumericCodeOfAuthor");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_GetAuthorIDLikeID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = codeCountry + "%";
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if (dt.Rows.Count > 0)
                {
                    AuthorID = dt.Rows[0][0].ToString();
                    convert = AuthorID.Substring(2, 4);
                    // if AuthorID begin not 0001
                    if (convert != "0001")
                        return "0001";
                    else
                    {
                        numeric = Convert.ToDecimal(convert);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            int j = convert.Length - numeric.ToString().Length;
                            AuthorID = convert.Substring(0, j); // get numbers of "0" front of numeric
                            code = Convert.ToString(AuthorID + numeric);
                            if (dt.Rows[i][0].ToString().Substring(2, 4) != code)
                                return code; // return numerics of AuthorID
                            numeric += 1; // Increase numeric 
                        }
                        String temp = numeric.ToString();
                        while (temp.Length < 4)
                        {
                            temp = "0" + temp;
                        }
                        return temp;
                    }
                }
                else
                    return "0001";
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void AddAuthorToDatabase(String AuthorID, String AuthorName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddAuthorToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID;
                cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = AuthorName;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void DeleteAuthorFromDatabase(String AuthorName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DeleteAuthorFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = AuthorName;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Deleted successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to delete this author because it's attribute!", "Error : Delete Author", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyAuthorFromDatabase(String AuthorID, String AuthorName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyAuthorFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID;
                cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = AuthorName;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Modified successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this author because it's attribute!", "Error : Modify Author", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayAuthor(String author, int check)
        {
            DataTable dt = new DataTable("DisplayAuthor");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Author From AuthorID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayAuthorFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = author;
                }
                // Display Author From AuthorName, check = 1
                else if (check == 1)
                {
                    cmd = new SqlCommand("BS_DisplayAuthorFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = author + "%";
                }
                // Display Author Like AuthorID, check = 2
                else
                {
                    cmd = new SqlCommand("BS_DisplayAuthorLikeID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = author + "%";
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllAuthor()
        {
            DataTable dt = new DataTable("DisplayAllAuthor");
            String sql = @"SELECT [AuthorID],[AuthorName]
                           FROM [BookStore].[dbo].[AUTHOR]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckAuthorInDataBase(String author, int check)
        {
            DataTable dt = new DataTable("CheckAuthorInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Author From AuthorID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckAuthorFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = author;
                }
                // Check Author From AuthorName, check = 1
                else if (check == 1)
                {
                    cmd = new SqlCommand("BS_CheckAuthorFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = author;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean CheckAuthorFromCountry(String AuthorName, String Country)
        {
            DataTable dt = new DataTable("CheckAuthorFromCountry");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_CheckAuthorFromCountry", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = AuthorName;
                cmd.Parameters.Add("@Country", SqlDbType.NVarChar).Value = Country + "%";
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean CheckAuthorName(String authorName)
        {
            for (int i = 0; i < authorName.Length; i++)
            {
                if ((authorName[i].ToString() == "\\") || (authorName[i].ToString() == ";") || (authorName[i].ToString() == ")"))
                    return false;
            }
            return true;
        }
    }
}
